﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Mengoo.DB
{
    public class DBHelperOledb : IDBHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string connectionString;

        /// <summary>
        /// 实例化
        /// </summary>
        /// <param name="connectionString"></param>
        public DBHelperOledb(string connectionString)
        {
            this.connectionString = connectionString;
        }



        #region IDBHelper 成员

        /// <summary>
        /// 执行SQL语句，返回受影响的行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public int Execute(string sql)
        {
            int RowsCount = -1;

            try
            {
                OleDbConnection Conn = new OleDbConnection(connectionString);

                OleDbCommand Comm = new OleDbCommand(sql, Conn);

                Conn.Open();

                RowsCount = Comm.ExecuteNonQuery();

                Conn.Close();
            }
            catch
            {
                RowsCount = 0;
            }
            return RowsCount;
        }

        /// <summary>
        /// 执行SQL语句，带SQL参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public int Execute(string sql, IDataParameter[] cmdParms)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, sql, cmdParms);

                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="sqlList"></param>
        /// <returns></returns>
        public int ExecuteTran(List<string> sqlList)
        {
            using (OleDbConnection Conn = new OleDbConnection(connectionString))
            {
                using (OleDbCommand Comm = new OleDbCommand())
                {
                    Conn.Open();
                    Comm.Connection = Conn;
                    using (OleDbTransaction Tran = Conn.BeginTransaction())
                    {
                        try
                        {
                            Comm.Transaction = Tran;
                            int count = 0;
                            for (int i = 0; i < sqlList.Count; i++)
                            {
                                string strSql = sqlList[i].ToString();

                                if (strSql.Trim().Length > 1)
                                {
                                    Comm.CommandText = strSql;
                                    count += Comm.ExecuteNonQuery();
                                }
                            }
                            Tran.Commit();
                            Conn.Close();
                            return count;
                        }
                        catch
                        {
                            Tran.Rollback();
                            Conn.Close();
                            return -1;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storeName"></param>
        /// <returns></returns>
        public DataSet ExecStoredProcedure(string storeName)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                using (OleDbDataAdapter da = new OleDbDataAdapter(storeName, conn))
                {
                    try
                    {
                        DataSet ds = new DataSet();
                        da.SelectCommand.CommandType = CommandType.StoredProcedure;
                        conn.Open();
                        da.Fill(ds);
                        return ds;
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                        da.SelectCommand.Parameters.Clear();
                    }
                }
            }
        }

        /// <summary>
        /// 执行存储过程，带参数
        /// </summary>
        /// <param name="storeName"></param>
        /// <param name="cmdParams"></param>
        /// <returns></returns>
        public DataSet ExecStoredProcedure(string storeName, params IDataParameter[] cmdParams)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                using (OleDbDataAdapter da = new OleDbDataAdapter(storeName, conn))
                {
                    try
                    {
                        DataSet ds = new DataSet();
                        foreach (IDataParameter tempSqlParameter in cmdParams)
                        {
                            da.SelectCommand.Parameters.Add(tempSqlParameter);
                        }
                        da.SelectCommand.CommandType = CommandType.StoredProcedure;
                        conn.Open();
                        da.Fill(ds);
                        return ds;
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }
                    finally
                    {
                        conn.Close();
                        da.SelectCommand.Parameters.Clear();
                    }
                }
            }
        }

        /// <summary>
        /// 执行sql语句，获取查询结果的datatable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();

            OleDbConnection Conn = new OleDbConnection(connectionString);

            Conn.Open();

            OleDbDataAdapter cmd = new OleDbDataAdapter(sql, Conn);

            cmd.Fill(dt);

            Conn.Close();

            return dt;
        }

        /// <summary>
        /// 用来从数据库中读取数据,返回一个DataSet,返回时多了一个tableName
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();

            OleDbConnection Conn = new OleDbConnection(connectionString);

            Conn.Open();

            OleDbDataAdapter cmd = new OleDbDataAdapter(sql, Conn);

            cmd.Fill(ds);

            Conn.Close();

            return ds;
        }

        /// <summary>
        /// 获取一个OledbDataReader对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public IDataReader GetDataReader(string sql)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    OleDbDataReader oread = cmd.ExecuteReader();

                    if (oread.Read())
                    {
                        cmd.Dispose();
                        return oread;
                    }
                    else
                    {
                        cmd.Dispose();
                        return null;
                    }
                }
            }
        }

        /// <summary>
        /// 获取第一行的指定字段值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        public object ReadField(string sql, string field)
        {
            return ReadField(sql, 0, field);
        }

        /// <summary>
        /// 获取指定行的指定字段值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="rowIndex"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        public object ReadField(string sql, int rowIndex, string field)
        {
            DataTable dt = GetDataTable(sql);

            if (dt.Rows.Count > rowIndex)
            {
                return dt.Rows[rowIndex][field];
            }
            return null;
        }

        #endregion



        #region 私有方法
        /// <summary>
        /// 匹配带参数的SQL语句
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, IDataParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (IDataParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        #endregion
    }
}

